{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import required libraries\n",
    "import pandas as pd\n",
    "\n",
    "# Read the CSV file\n",
    "file_name = \"../fra.trades.csv\"\n",
    "df = pd.read_csv(file_name)\n",
    "\n",
    "row_count = df.shape[0]\n",
    "# drop header rows that appear multiple times in the csv file\n",
    "df = df.drop(df[df.timestamp == \"timestamp\"].index)\n",
    "\n",
    "restart_count = row_count - df.shape[0]\n",
    "\n",
    "# timestamp,uuid,landed,accepted,rejected,errorType,errorContent,txn0Signature,txn1Signature,txn2Signature,arbSize,expectedProfit,hop1Dex,hop2Dex,sourceMint,intermediateMint,tipLamports,mempoolEnd,preSimEnd,simEnd,postSimEnd,calcArbEnd,buildBundleEnd,bundleSent\n",
    "\n",
    "df['timestamp'] = pd.to_datetime(df['timestamp'], unit='ms')\n",
    "df['mempoolEnd'] = pd.to_datetime(df['mempoolEnd'], unit='ms')\n",
    "df['preSimEnd'] = pd.to_datetime(df['preSimEnd'], unit='ms')\n",
    "df['simEnd'] = pd.to_datetime(df['simEnd'], unit='ms')\n",
    "df['postSimEnd'] = pd.to_datetime(df['postSimEnd'], unit='ms')\n",
    "df['calcArbEnd'] = pd.to_datetime(df['calcArbEnd'], unit='ms')\n",
    "df['buildBundleEnd'] = pd.to_datetime(df['buildBundleEnd'], unit='ms')\n",
    "df['bundleSent'] = pd.to_datetime(df['bundleSent'], unit='ms')\n",
    "df[['landed', 'rejected']] = df[['landed', 'rejected']].fillna(\n",
    "    False).astype('bool')\n",
    "df[['accepted', 'arbSize', 'expectedProfit', 'tipLamports']] = df[[\n",
    "    'accepted', 'arbSize', 'expectedProfit', 'tipLamports']].astype('int64')\n",
    "\n",
    "# Display the first few records\n",
    "print(df.head())\n",
    "print(df.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Reading file: \", file_name)\n",
    "print(\"First item timestamp (UTC): \", df['timestamp'].iloc[0])\n",
    "print(f\"Restart count: {restart_count}\")\n",
    "\n",
    "LAMPORTS_PER_SOL = 1000000000\n",
    "\n",
    "# Calculate the percentage of rows where 'landed' is True compared to those with 'accepted' > 0\n",
    "success_landed = df[(df['landed'] == True) & (df['accepted'] > 0)]\n",
    "success_landed_count = success_landed.shape[0]\n",
    "total_lamports_tipped = success_landed['tipLamports'].sum()\n",
    "accepted_gt_0 = df[df['accepted'] > 0].shape[0]\n",
    "percentage_landed_accepted = (success_landed_count / accepted_gt_0) * 100\n",
    "\n",
    "# Calculate the percentage of rows with 'accepted' > 0 compared to the total\n",
    "total_rows = df.shape[0]\n",
    "percentage_accepted = (accepted_gt_0 / total_rows) * 100\n",
    "\n",
    "# Print the results\n",
    "print(f\"Total sent: {total_rows}, accepted: {accepted_gt_0}, landed: {success_landed_count}\")\n",
    "print(\n",
    "    f\"Percentage landed of accepted: {percentage_landed_accepted:.2f}%\")\n",
    "print(\n",
    "    f\"Percentage accepted of sent: {percentage_accepted:.2f}%\")\n",
    "print(f\"Total lamports tipped: {total_lamports_tipped} ({total_lamports_tipped / LAMPORTS_PER_SOL:.6f} SOL)\")\n",
    "\n",
    "# Find the error percentage for different error messages\n",
    "errors_filtered = df[df['errorType'].notnull()]\n",
    "sim_failures = errors_filtered[errors_filtered['errorType'].str.contains(\n",
    "    \"simulationFailure\")]\n",
    "total_errors = errors_filtered.shape[0]\n",
    "\n",
    "sim_errors_my_fault = sim_failures[sim_failures.apply(\n",
    "    lambda row: row['txn1Signature'] in row['errorContent'] or row['txn2Signature'] in row['errorContent'], axis=1)]\n",
    "sim_errors_backrun_txn = sim_failures[sim_failures.apply(\n",
    "    lambda row: row['txn0Signature'] in row['errorContent'], axis=1)]\n",
    "sim_tx_already_processed = sim_failures[sim_failures['errorContent'].str.contains(\n",
    "    \"This transaction has already been processed\")]\n",
    "sim_other = sim_failures[~sim_failures.index.isin(\n",
    "    sim_errors_my_fault.index.union(\n",
    "        sim_errors_backrun_txn.index).union(sim_tx_already_processed.index))]\n",
    "\n",
    "# Group non-simulation errors by errorType\n",
    "non_sim_errors = errors_filtered[~errors_filtered['errorType'].str.contains(\"simulationFailure\")]\n",
    "sending_errors = errors_filtered[errors_filtered['errorType'].str.contains(\"sendingError\")]\n",
    "\n",
    "error_groups = non_sim_errors.groupby(\"errorType\").size().reset_index(name='count')\n",
    "\n",
    "# Calculate the error totals and percentages for sim errors and other errors\n",
    "error_totals = {\n",
    "    'sim_errors_my_fault': sim_errors_my_fault.shape[0],\n",
    "    'sim_errors_backrun_txn': sim_errors_backrun_txn.shape[0],\n",
    "    'sim_tx_already_processed': sim_tx_already_processed.shape[0],\n",
    "    'sim_other': sim_other.shape[0],\n",
    "}\n",
    "\n",
    "error_percentages = {k: (v / total_errors) * 100 for k, v in error_totals.items()}\n",
    "for _, row in error_groups.iterrows():\n",
    "    error_name = f\"{row['errorType']}\"\n",
    "    error_totals[error_name] = row['count']\n",
    "    error_percentages[error_name] = (row['count'] / total_errors) * 100\n",
    "\n",
    "# Remove errors with zero count\n",
    "error_totals = {k: v for k, v in error_totals.items() if v > 0}\n",
    "error_percentages = {k: v for k, v in error_percentages.items() if k in error_totals}\n",
    "\n",
    "# Print the error totals and their percentages\n",
    "print(\"\\nError Totals and Percentages:\")\n",
    "for error_type, total in error_totals.items():\n",
    "    percentage = error_percentages[error_type]\n",
    "    print(f\"{error_type}: Total: {total}, Percentage: {percentage:.2f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0x1771 is Jupiter SlippageToleranceExceeded\n",
    "\n",
    "print(sending_errors.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
